/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package suministros.ejb30.DAO;
import javax.persistence.PersistenceUnit;
import suministros.ejb30.entity.Cargo;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.Date;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.annotation.PostConstruct;
import javax.annotation.PreDestroy;
import javax.annotation.Resource;
import javax.persistence.EntityManager;
import javax.persistence.EntityManagerFactory;
import javax.sql.DataSource;
import javax.ejb.Stateless;
import javax.persistence.EntityTransaction;
import javax.persistence.Query;
import suministros.ejb30.TO.CargoTO;
import suministros.ejb30.TO.DetalleUsuarioCargoTO;
import suministros.ejb30.entity.HistorialCargos;
import suministros.ejb30.entity.HistorialCargosPK;
import suministros.ejb30.helper.Utiles_Convertir;


/**
 *
 * @author CAROLINA
 */
@Stateless
public class GestionCargosBean implements GestionCargosLocal {

 @PersistenceUnit(unitName="suministros-pu")
  EntityManagerFactory emf;

  EntityManager em;

  @Resource(name="jdbc/Administracion")
  DataSource dsAdministracion;

  @PostConstruct
  public void inicializaBean()
  {
    this.em = this.emf.createEntityManager();
  }

  @PreDestroy
  public void finalizaBean() {
    this.em.close();
  }//
  public String getIdCargo() throws Exception
  {

    String retorno = null;
    Connection cnx = null;
    try {

      cnx = this.dsAdministracion.getConnection();
      CallableStatement csLlamada = cnx.prepareCall("{? = call [dbo].[f_sumi_getcodigoCargo]()}");
      csLlamada.registerOutParameter(1, 12);
      csLlamada.execute();
      retorno = csLlamada.getString(1);
    }
    catch (SQLException ex) {
      System.out.print(ex.getMessage());
    } finally {
      try {
        cnx.close();
      } catch (Exception ex) {
        Logger.getLogger(SumiGestionPedidoDAO.class.getName()).log(Level.SEVERE, null, ex);
      }
    }
    return retorno;
  }//
   public String getIdCargoHistorial(String dni) throws Exception
  {

    String retorno = null;
    Connection cnx = null;
    try {

      cnx = this.dsAdministracion.getConnection();
      CallableStatement csLlamada = cnx.prepareCall("{? = call [dbo].[f_sumi_getcodigoCargoHistorial](?)}");
      csLlamada.registerOutParameter(1, 12);
      csLlamada.setString(2, dni);
      csLlamada.execute();
      retorno = csLlamada.getString(1);
    }
    catch (SQLException ex) {
      System.out.print(ex.getMessage());
    } finally {
      try {
        cnx.close();
      } catch (Exception ex) {
        Logger.getLogger(SumiGestionPedidoDAO.class.getName()).log(Level.SEVERE, null, ex);
      }
    }
    return retorno;
  }//
  public String insertarCargo(Cargo cargo) throws Exception
  {
    EntityTransaction tx = this.em.getTransaction();
    String codigo = this.getIdCargo();
    tx.begin();
    try {
          cargo.setCodCargo(codigo);
          cargo.setFlagVigente(true);
          this.em.persist(cargo);
          this.em.flush();
          tx.commit();

    } catch (Exception e) {
      tx.rollback();
      throw e;
    }
    return codigo;

}
   public String modificarCargo(Cargo cargo) throws Exception
  {
    EntityTransaction tx = this.em.getTransaction();
    String codigo = this.getIdCargo();
    tx.begin();
    try {
          Cargo c= new Cargo();
          c=this.em.find(Cargo.class, cargo.getCodCargo());
          c.setCargo(cargo.getCargo());
          c.setFlagVigente(cargo.getFlagVigente());
          this.em.merge(c);
          this.em.flush();
          tx.commit();

    } catch (Exception e) {
      tx.rollback();
      throw e;
    }
    return codigo;

}

 public String insertarHistorialCargo(Cargo cargo) throws Exception
  {
    EntityTransaction tx = this.em.getTransaction();
    String codigo = this.getIdCargo();
    tx.begin();
    try {
          cargo.setCodCargo(codigo);
          cargo.setFlagVigente(true);
          this.em.persist(cargo);
          this.em.flush();
          tx.commit();

    } catch (Exception e) {
      tx.rollback();
      throw e;
    }
    return codigo;

}
  public boolean insertarusuarioRol(HistorialCargos detallesa,HistorialCargos detallese) throws Exception{
      boolean retorno=false;
      EntityTransaction tx = this.em.getTransaction();
      tx.begin();
      try {
           HistorialCargosPK nuevo= new HistorialCargosPK();
           nuevo.setDni(detallesa.getHistorialCargosPK().getDni());
           nuevo.setCodHistorialCargo(this.getIdCargoHistorial(detallesa.getHistorialCargosPK().getDni()));
           detallesa.setHistorialCargosPK(nuevo);
           detallesa.setCodcondicion("2");
           em.persist(detallesa);

           HistorialCargos viejo= new HistorialCargos();
           viejo=this.em.find(HistorialCargos.class, detallese.getHistorialCargosPK());
           viejo.setFechaFinal(detallesa.getFechaInicio());
           em.merge(viejo);

          this.em.flush();
          tx.commit();
          retorno = true;
      }
      catch (Exception e) {
      tx.rollback();
      throw e;
      }
      return retorno;


  }
public List<DetalleUsuarioCargoTO> obtenerDetalleUsuarioCargo(String codigo){
      Query consulta = this.em.createNativeQuery("select d.Cod_Cargo, d.DNI, r.Cargo, d.Fecha_Inicio," +
              " d.cod_Historial_Cargo from historial_cargos d inner join cargo r on d.Cod_Cargo= r.Cod_Cargo  " +
              " where d.DNI='"+codigo+"' order by  d.cod_Historial_Cargo asc");
       return Utiles_Convertir.convertirToBeanBinding_UsuarioCargo(consulta.getResultList());
  }
 public List<CargoTO> getListaCargos() throws Exception {
    Query consulta = this.em.createNativeQuery("select Cod_Cargo, Cargo from cargo");
    return Utiles_Convertir.convertirToBeanBinding_Cargo(consulta.getResultList());
  }
 
}
